package dao;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.Debug;

import dao.interfaces.IRecipeDAO;
import model.Recipe;

public class RecipeDAO extends DAO<Recipe> implements IRecipeDAO<Recipe> {
	
	public RecipeDAO(Connection conn) {
		super(conn);
	}
	
	/**
	 * implements the inherited abstract methods from the DAO<User> abstract class
	 */
	
	@Override
	public boolean insert(Recipe aRecette) {
		String sql =
			"INSERT INTO cook_recipe " +
			"(recipe_name, recipe_description, recipe_time_preparation, " +
			"recipe_time_cooking, recipe_preparation, recipe_ingredient, " +
			"recipe_quantity_people, recipe_image, recipe_date, " +
			"user_id, category_id) " +
			"VALUES (\"" + 
			aRecette.getName().replace("'", "\'") + "\", \"" + aRecette.getDescription().replace("'", "\'") + "\", " + aRecette.getTime_preparation() + ", " + 
			aRecette.getTime_cooking() + ", \"" + aRecette.getPreparation().replace("'", "\'") + "\", \"" + aRecette.getIngredient().replace("'", "\'") + "\", " + 
			aRecette.getQuantity_people() + ", \"" + aRecette.getImage() +"\" , DATE(NOW()), " + 
			aRecette.getUser_id() + ", " + aRecette.getCategory_id() +")";
			System.out.println(sql);
			Debug.log(sql);
		try {
			Statement stm = connection.createStatement();
			stm.executeUpdate(sql);
			stm.close();
			return true;
			} 
		catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
	}
	
	@Override
	public boolean delete(Recipe obj) {
		return false;
	}
	
	@Override
	public boolean update(Recipe obj) {
		return false;
	}
	
	@Override
	public Recipe findById(int recipe_id) {
		String sql =
			"SELECT * " +
			"FROM cook_recipe " +
			"WHERE recipe_id = " + recipe_id;
		Debug.log(sql);
		try {
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			
			if (rs.next()) {
				Recipe recipe = new Recipe();
				recipe.setId(rs.getInt("recipe_id"));
				recipe.setName(rs.getString("recipe_name"));
				recipe.setDescription(rs.getString("recipe_description"));
				recipe.setQuantity_people(rs.getInt("recipe_quantity_people"));
				recipe.setTime_preparation(rs.getInt("recipe_time_preparation"));
				recipe.setTime_cooking(rs.getInt("recipe_time_cooking"));
				recipe.setIngredient(rs.getString("recipe_ingredient"));
				recipe.setPreparation(rs.getString("recipe_preparation"));
				recipe.setImage(rs.getString("recipe_image"));
				recipe.setUser_id(rs.getInt("user_id"));
				recipe.setCategory_id(rs.getInt("category_id"));
				
				return recipe;
			}
		}
		catch (SQLException sqle) {
			System.out.println(sql);
			sqle.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<Recipe> getAll() {
		List<Recipe> recipeList = new ArrayList<Recipe>();
		
		try {
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM cook_recipe ORDER BY recipe_id DESC");
			
			while (rs.next()) {
				Recipe recipe = new Recipe();
				recipe.setId(rs.getInt("recipe_id"));
				recipe.setName(rs.getString("recipe_name"));
				recipe.setDescription(rs.getString("recipe_description"));
				recipe.setQuantity_people(rs.getInt("recipe_quantity_people"));
				recipe.setTime_preparation(rs.getInt("recipe_time_preparation"));
				recipe.setTime_cooking(rs.getInt("recipe_time_cooking"));
				recipe.setIngredient(rs.getString("recipe_ingredient"));
				recipe.setPreparation(rs.getString("recipe_preparation"));
				recipe.setImage(rs.getString("recipe_image"));
				recipe.setUser_id(rs.getInt("user_id"));
				recipe.setCategory_id(rs.getInt("category_id"));
				
				recipeList.add(recipe);
			}
			
			if (recipeList.size() > 0)
				return recipeList;
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}		
		return null;
	}
	
	/**
	 * implements the inherited abstract methods from the IRecipeDAO<Recipe> interface
	 */
	
	@Override
	public List<Recipe> search(String keyword, int category_id) {
//		String keywordCond = " (recipe_name LIKE '%" + keyword + "%' " +
//						   " OR recipe_description LIKE '%" + keyword + "%') ";
		String keywordCond = " (recipe_name LIKE '%" + keyword + "%' ) ";

		String catCond = (category_id == 0) ? " 1 = 1 " : " category_id = " + category_id + " ";
		
		String sql = "SELECT * FROM cook_recipe WHERE " + keywordCond + " AND " + catCond + " ORDER BY recipe_id DESC";
		
		Debug.log(sql);
		List<Recipe> recipeList = new ArrayList<Recipe>();
		
		try {
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				Recipe recipe = new Recipe();
				recipe.setId(rs.getInt("recipe_id"));
				recipe.setName(rs.getString("recipe_name"));
				recipe.setDescription(rs.getString("recipe_description"));
				recipe.setQuantity_people(rs.getInt("recipe_quantity_people"));
				recipe.setTime_preparation(rs.getInt("recipe_time_preparation"));
				recipe.setTime_cooking(rs.getInt("recipe_time_cooking"));
				recipe.setIngredient(rs.getString("recipe_ingredient"));
				recipe.setPreparation(rs.getString("recipe_preparation"));
				recipe.setImage(rs.getString("recipe_image"));
				recipe.setUser_id(rs.getInt("user_id"));
				recipe.setCategory_id(rs.getInt("category_id"));
				
				recipeList.add(recipe);
			}
			if (recipeList.size() > 0)
				return recipeList;
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return null;
	}
}
